/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pv168.uloha3.managers.impl;

import cz.muni.fi.pv168.uloha3.common.DBUtils;
import cz.muni.fi.pv168.uloha3.entities.RealEstate;
import cz.muni.fi.pv168.uloha3.entities.RealEstateType;
import cz.muni.fi.pv168.uloha3.exceptions.RealEstateManagerException;
import cz.muni.fi.pv168.uloha3.managers.RealEstateManager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author stefan
 */
public class RealEstateManagerImpl implements RealEstateManager{

    private static final Logger logger = Logger.getLogger(RealEstateManagerImpl.class.getName());
    
    private DataSource dataSource;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private void checkDataSource() {
        if (dataSource == null) {
            throw new IllegalStateException("DataSource is not set");
        }
    }
    
    static {
        try {
            RealEstateManagerImpl.class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(RealEstateManagerImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private static final String URL = "jdbc:derby://localhost:1527/pv168";
    private static final String USERNAME = "uzivatel";
    private static final String PASSWORD = "heslo";
    
    private static final String PREPARED_STATEMENT_ADD_REALESTATE_STR = "INSERT INTO REALESTATE (CATASTRAL_ID, TYPE, ADDRESS) VALUES (?, ?, ?)";
    private static final String PREPARED_STATEMENT_EDIT_REALESTATE_STR = "UPDATE REALESTATE SET CATASTRAL_ID=?, TYPE=?, ADDRESS=? WHERE ID=?";
    private static final String PREPARED_STATEMENT_GET_REALESTATES_STR = "SELECT ID, CATASTRAL_ID, TYPE, ADDRESS FROM REALESTATE";
    private static final String PREPARED_STATEMENT_DELETE_REALESTATE_STR = "DELETE FROM REALESTATE WHERE ID=?";  
    private static final String PREPARED_STATEMENT_GET_REALESTATE_BY_ID_STR = "SELECT ID, CATASTRAL_ID, TYPE, ADDRESS FROM REALESTATE WHERE ID=?";  

    
    public void addRealEstate(RealEstate realEstate) throws RealEstateManagerException{
        checkDataSource();
        if (realEstate == null) {
            throw new RealEstateManagerException("Provided realEstate cannot be null");
        }
        if (realEstate.getId() != null) {
            throw new RealEstateManagerException("realEstate is already set");
        }
        if (realEstate.getCatastralId() == null || realEstate.getCatastralId().isEmpty()) {
            throw new RealEstateManagerException("catestralId cannot be null");
        }
        if (realEstate.getType() == null) {
            throw new RealEstateManagerException("type cannot be null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_ADD_REALESTATE_STR, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, realEstate.getCatastralId());
            preparedStatement.setInt(2, realEstate.getType().ordinal());
            preparedStatement.setString(3, realEstate.getAddress());
            
            preparedStatement.executeUpdate();
            
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                Long realEstateId = generatedKeys.getLong(1);
                realEstate.setId(realEstateId);
            } else {
                throw new RealEstateManagerException("No primary key generated for new RealEstate " + realEstate);
            }
            
            connection.commit();
        } catch (SQLException ex) {
            String msg = "Error when adding realEstate into db";
            logger.log(Level.SEVERE, msg, ex);
            throw new RealEstateManagerException("SQLException caught while trying to add RealEstate " + realEstate + " to database: " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
        
    }

    public void editRealEstate(RealEstate realEstate) throws RealEstateManagerException {
        checkDataSource();
        if (realEstate == null) {
            throw new RealEstateManagerException("Provided realEstate cannot be null");
        }
        if (realEstate.getId() == null) {
            throw new RealEstateManagerException("id cannot be null");
        }
        if (realEstate.getCatastralId() == null || realEstate.getCatastralId().isEmpty()) {
            throw new RealEstateManagerException("catestralId cannot be null");
        }
        if (realEstate.getType() == null) {
            throw new RealEstateManagerException("type cannot be null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_EDIT_REALESTATE_STR);
            preparedStatement.setString(1, realEstate.getCatastralId());
            preparedStatement.setInt(2, realEstate.getType().ordinal());
            preparedStatement.setString(3, realEstate.getAddress());
            preparedStatement.setLong(4, realEstate.getId());
            
            preparedStatement.executeUpdate();
            
            connection.commit();
        } catch (SQLException ex) {
            String msg = "Error when updating realEstate in the db";
            logger.log(Level.SEVERE, msg, ex);
            throw new RealEstateManagerException("SQLException caught while trying to edit RealEstate " + realEstate + " to database: " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
    }

    public void deleteRealEstate(RealEstate realEstate) throws RealEstateManagerException {
        checkDataSource();
        if (realEstate == null) {
            throw new RealEstateManagerException("Provided realEstate cannot be null");
        }
        if (realEstate.getId() == null) {
            throw new RealEstateManagerException("id cannot be null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_DELETE_REALESTATE_STR);
            preparedStatement.setLong(1, realEstate.getId());
            
            preparedStatement.executeUpdate();
            
            connection.commit();
        } catch (SQLException ex) {
            String msg = "Error when deleting realEstate from the db";
            logger.log(Level.SEVERE, msg, ex);
            throw new RealEstateManagerException("SQLException caught while trying to delete RealEstate " + realEstate + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
        
    }

    public Set<RealEstate> listRealEstates() throws RealEstateManagerException{
        checkDataSource();
        Set<RealEstate> result = new HashSet<RealEstate>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_REALESTATES_STR);
            
            ResultSet resultSet = preparedStatement.executeQuery();
            RealEstate realEstate;
            
            while(resultSet.next()) {
                realEstate = new RealEstate();
                realEstate.setId(resultSet.getLong(1));
                realEstate.setCatastralId(resultSet.getString(2));
                // TODO: string instead of enum would be better
                 realEstate.setType(RealEstateType.fromOrdinal(resultSet.getInt(3))); //((resultSet.getInt(3)));
                realEstate.setAddress(resultSet.getString(4));
                result.add(realEstate);
            }
            
        } catch (SQLException ex) {
            String msg = "Error when getting all realEstates from DB";
            logger.log(Level.SEVERE, msg, ex);
            throw new RealEstateManagerException("SQLException caught while trying to get RealEstates " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }   
        
        return result;
    }

    public RealEstate getRealEstateById(Long id) throws RealEstateManagerException{
        checkDataSource();
        
        if (id == null) {
            throw new IllegalArgumentException("id is null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        RealEstate realEstate = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_REALESTATE_BY_ID_STR);
            preparedStatement.setLong(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            
            while(resultSet.next()) {
                realEstate = new RealEstate();
                realEstate.setId(resultSet.getLong(1));
                realEstate.setCatastralId(resultSet.getString(2));
                // TODO: string instead of enum would be better
                realEstate.setType(RealEstateType.fromOrdinal(resultSet.getInt(3))); //((resultSet.getInt(3)));
                realEstate.setAddress(resultSet.getString(4));
            }
            
        return realEstate;
        } catch (SQLException ex) {
            String msg = "Error when getting realEstate with id = " + id + " from DB";
            logger.log(Level.SEVERE, msg, ex);
            throw new RealEstateManagerException("SQLException caught while trying to get RealEstate " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }   
        
    }
    
}
